﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Models;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace DAL
{
    public class TarefasDAL
    {
        private string connectionString = ConfigurationManager.ConnectionStrings["ScrumDB"].ConnectionString;

        public List<Tarefas> Listar(int CodigoIteracao)
        {
            List<Tarefas> lista = new List<Tarefas>();
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT CodigoTarefa,CodigoProjeto,CodigoIteracao,NomeTarefa,TipoTarefa,HorasPrevistas,ValorNegocio,SituacaoTarefa FROM Tarefas WHERE CodigoIteracao = @CodigoIteracao";

            SqlParameter parameter = command.CreateParameter();
            parameter.ParameterName = "CodigoIteracao";
            parameter.Value = CodigoIteracao;
            parameter.DbType = DbType.Int32;
            command.Parameters.Add(parameter);

            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                lista.Add(
                    new Tarefas
                    {
                        CodigoTarefa = reader.GetInt32(0),
                        CodigoProjeto = reader.GetInt32(1),
                        CodigoIteracao = reader.GetInt32(2),
                        NomeTarefa = reader.GetString(3),
                        TipoTarefa = reader.GetString(4),
                        HorasPrevistas = reader.GetDecimal(5),
                        ValorNegocio = reader.GetInt32(6),
                        SituacaoTarefa = reader.GetString(7)
                    }
                    );
            }
            reader.Close();
            connection.Close();

            return lista;
        }

        public List<Tarefas> ListarGeral()
        {
            List<Tarefas> lista = new List<Tarefas>();
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT CodigoTarefa,CodigoProjeto,CodigoIteracao,NomeTarefa,TipoTarefa,HorasPrevistas,ValorNegocio,SituacaoTarefa FROM Tarefas";

            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                lista.Add(
                    new Tarefas
                    {
                        CodigoTarefa = reader.GetInt32(0),
                        CodigoProjeto = reader.GetInt32(1),
                        CodigoIteracao = reader.GetInt32(2),
                        NomeTarefa = reader.GetString(3),
                        TipoTarefa = reader.GetString(4),
                        HorasPrevistas = reader.GetDecimal(5),
                        ValorNegocio = reader.GetInt32(6),
                        SituacaoTarefa = reader.GetString(7)
                    }
                    );
            }
            reader.Close();
            connection.Close();

            return lista;
        }


        
        public Tarefas Ler(int codigoTarefa)
        {
            Tarefas instancia = null;

            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "SELECT CodigoTarefa,CodigoProjeto,CodigoIteracao,NomeTarefa,TipoTarefa,HorasPrevistas,ValorNegocio,SituacaoTarefa FROM Tarefas WHERE CodigoTarefa=@CodigoTarefa";
            SqlParameter parameter = command.CreateParameter();
            parameter.ParameterName = "CodigoTarefa";
            parameter.Value = codigoTarefa;
            parameter.DbType = DbType.Int32;
            command.Parameters.Add(parameter);

            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                instancia =
                    new Tarefas
                    {
                        CodigoTarefa = reader.GetInt32(0),
                        CodigoProjeto = reader.GetInt32(1),
                        CodigoIteracao = reader.GetInt32(2),
                        NomeTarefa = reader.GetString(3),
                        TipoTarefa = reader.GetString(4),
                        HorasPrevistas = reader.GetDecimal(5),
                        ValorNegocio = reader.GetInt32(6),
                        SituacaoTarefa = reader.GetString(7)
                    };
            }
            reader.Close();
            connection.Close();

            return instancia;
        }

        public void Incluir(Tarefas instancia)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = connection.CreateCommand();
            command.CommandText = @"INSERT INTO Tarefas (CodigoProjeto,CodigoIteracao,NomeTarefa,TipoTarefa,HorasPrevistas,ValorNegocio,SituacaoTarefa)
                                    VALUES(@CodigoProjeto,@CodigoIteracao,@NomeTarefa,@TipoTarefa,@HorasPrevistas,@ValorNegocio,@SituacaoTarefa)";
            SqlParameter parameter1 = command.CreateParameter();
            parameter1.ParameterName = "CodigoProjeto";
            parameter1.Value = instancia.CodigoProjeto;
            parameter1.DbType = DbType.Int32;
            command.Parameters.Add(parameter1);

            SqlParameter parameter2 = command.CreateParameter();
            parameter2.ParameterName = "CodigoIteracao";
            parameter2.Value = instancia.CodigoIteracao;
            parameter2.DbType = DbType.Int32;
            command.Parameters.Add(parameter2);

            SqlParameter parameter3 = command.CreateParameter();
            parameter3.ParameterName = "NomeTarefa";
            parameter3.Value = instancia.NomeTarefa;
            parameter3.DbType = DbType.String;
            command.Parameters.Add(parameter3);

            SqlParameter parameter4 = command.CreateParameter();
            parameter4.ParameterName = "TipoTarefa";
            parameter4.Value = instancia.TipoTarefa;
            parameter4.DbType = DbType.String;
            command.Parameters.Add(parameter4);

            SqlParameter parameter5 = command.CreateParameter();
            parameter5.ParameterName = "HorasPrevistas";
            parameter5.Value = instancia.HorasPrevistas;
            parameter5.DbType = DbType.Decimal;
            command.Parameters.Add(parameter5);

            SqlParameter parameter6 = command.CreateParameter();
            parameter6.ParameterName = "ValorNegocio";
            parameter6.Value = instancia.ValorNegocio;
            parameter6.DbType = DbType.Int32;
            command.Parameters.Add(parameter6);

            SqlParameter parameter7 = command.CreateParameter();
            parameter7.ParameterName = "SituacaoTarefa";
            parameter7.Value = instancia.SituacaoTarefa;
            parameter7.DbType = DbType.String;
            command.Parameters.Add(parameter7);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public void Alterar(Tarefas instancia)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = connection.CreateCommand();
            command.CommandText = @"UPDATE Tarefas
                                    SET CodigoProjeto=@CodigoProjeto,
                                        CodigoIteracao=@CodigoIteracao,
                                        NomeTarefa=@NomeTarefa,
                                        TipoTarefa=@TipoTarefa,
                                        HorasPrevistas=@HorasPrevistas,
                                        ValorNegocio=@ValorNegocio,
                                        SituacaoTarefa=@SituacaoTarefa WHERE CodigoTarefa=@CodigoTarefa";

            SqlParameter parameter1 = command.CreateParameter();
            parameter1.ParameterName = "CodigoProjeto";
            parameter1.Value = instancia.CodigoProjeto;
            parameter1.DbType = DbType.Int32;
            command.Parameters.Add(parameter1);

            SqlParameter parameter2 = command.CreateParameter();
            parameter2.ParameterName = "CodigoIteracao";
            parameter2.Value = instancia.CodigoIteracao;
            parameter2.DbType = DbType.Int32;
            command.Parameters.Add(parameter2);

            SqlParameter parameter3 = command.CreateParameter();
            parameter3.ParameterName = "NomeTarefa";
            parameter3.Value = instancia.NomeTarefa;
            parameter3.DbType = DbType.String;
            command.Parameters.Add(parameter3);

            SqlParameter parameter4 = command.CreateParameter();
            parameter4.ParameterName = "TipoTarefa";
            parameter4.Value = instancia.TipoTarefa;
            parameter4.DbType = DbType.String;
            command.Parameters.Add(parameter4);

            SqlParameter parameter5 = command.CreateParameter();
            parameter5.ParameterName = "HorasPrevistas";
            parameter5.Value = instancia.HorasPrevistas;
            parameter5.DbType = DbType.Decimal;
            command.Parameters.Add(parameter5);

            SqlParameter parameter6 = command.CreateParameter();
            parameter6.ParameterName = "ValorNegocio";
            parameter6.Value = instancia.ValorNegocio;
            parameter6.DbType = DbType.Int32;
            command.Parameters.Add(parameter6);

            SqlParameter parameter7 = command.CreateParameter();
            parameter7.ParameterName = "SituacaoTarefa";
            parameter7.Value = instancia.SituacaoTarefa;
            parameter7.DbType = DbType.String;
            command.Parameters.Add(parameter7);

            SqlParameter parameter8 = command.CreateParameter();
            parameter8.ParameterName = "CodigoTarefa";
            parameter8.Value = instancia.CodigoTarefa;
            parameter8.DbType = DbType.Int32;
            command.Parameters.Add(parameter8);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }

        public void Excluir(int codigoTarefa)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            SqlCommand command = connection.CreateCommand();
            command.CommandText = "DELETE FROM Tarefas WHERE CodigoTarefa=@CodigoTarefa";

            SqlParameter parameter = command.CreateParameter();
            parameter.ParameterName = "CodigoTarefa";
            parameter.Value = codigoTarefa;
            parameter.DbType = DbType.Int32;
            command.Parameters.Add(parameter);

            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();
        }
    }
}
